﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class Search
    {
        private DbHelperSQL dbhelper = new DbHelperSQL();

        #region 获得搜索建议词
        /// <summary>
        /// 获得搜索建议词
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public List<MODEL.Search> getSuggestion(string keyword)
        {
            string sql = "select top 5 KeyWord,Count(*) as SearchCount from Search"+
                         "   where datediff(day,AddTime,getdate())<7 and KeyWord like @kw"+
                         "   group by KeyWord"+
                         "   order by Count(*) desc";
            SqlParameter[] paras = {
                                      new SqlParameter("@kw","%"+keyword+"%")
                                   };
            DataTable dt = dbhelper.GetDataTable(sql, paras);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                List<MODEL.Search> list = new List<MODEL.Search>();
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Search search = new MODEL.Search();
                    search.KeyWord = dr["KeyWord"].ToString();
                    list.Add(search);
                }
                return list;
            }
        } 
        #endregion

        #region 获得前三的热词
        /// <summary>
        /// 获得前三的热词
        /// </summary>
        /// <returns></returns>
        public List<MODEL.Search> getKeyWord()
        {
            string sql = "select top 3 KeyWord,Count(*) as SearchCount from Search" +
                          "  where datediff(day,AddTime,getdate())<7" +
                          "  group by KeyWord" +
                          "  order by Count(*) desc";
            DataTable dt = new DataTable();
            dt = dbhelper.GetDataTable(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                List<MODEL.Search> list = new List<MODEL.Search>();
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Search search = new MODEL.Search();
                    search.KeyWord = dr["KeyWord"].ToString();
                    list.Add(search);
                }
                return list;
            }
        } 
        #endregion

        #region 插入热词
        /// <summary>
        /// 插入热词
        /// </summary>
        /// <param name="word"></param>
        /// <returns></returns>
        public bool addKeyWord(string word)
        {
            string sql = "insert into Search(KeyWord) values(@KeyWord);";
            SqlParameter[] paras = { 
                                      new SqlParameter("@KeyWord",word)
                                   };
            int r = dbhelper.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion
    }
}
